import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.io.Writer;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
import java.util.Scanner;

import model.DetectionParameters;
import model.RelationalDatabaseManagementSystem;
import model.SystemConfiguration;
import model.TrainingParameters;
import utils.Chronograph;
import clustering.Clusterer;
import clustering.ClustersDataGenerator;

import com.google.gson.Gson;
import com.google.gson.GsonBuilder;

public class QueryDbInfo {

	String[] voz_fraudsters = { "0034515647715", "0034515621702",
			"0034515647085", "0034515646913", "0034515646499", "0034515646391",
			"0034515646949", "0034515646288", "0034515646824", "0034902946503",
			"0034902906370", "0034902906471", "0034902909120", "0034933968711",
			"0034515647311", "0034902909502", "009987803898" };

	public static void main(String[] args) throws Exception {

		QueryDbInfo app = new QueryDbInfo();
		System.out
				.println("1. All subscribers from table USERS (TABLE USERS IS NOT USED!)");
		System.out.println("2. All subscribers ordered by calls number");
		System.out.println("3. Data for weka clusterer (not tested");
		System.out.println("4. Save parameters using json");
		System.out.println("5. Read parameters using json");
		System.out
				.println("6. Distribution of the calls with respect to the call state");
		System.out.println("7. Hijacked accounts reported by Voz");
		System.out.println("8. Export calls for Tasos");

		Scanner scan = new Scanner(System.in);
		int c = scan.nextInt();
		Chronograph chronos = new Chronograph();
		chronos.reset();
		if (c == 1) {
			app.scenario1();
		} else if (c == 2) {
			app.scenario2();
		} else if (c == 3) {
			app.scenario3();
		} else if (c == 4) {
			app.scenario4();
		} else if (c == 5) {
			app.scenario5();
		} else if (c == 6) {
			app.scenario6();
		} else if (c == 7) {
			app.scenario7();
		} else if (c == 8) {
			app.scenario8();
		}
		chronos.lap();
		System.out.println("Elasped time " + chronos.getElapsedTimeAsString());
	}

	private Connection connect = null;
	private Statement statement = null;
	private ResultSet resultSet = null;
	SystemConfiguration systemConfiguration = null;

	public QueryDbInfo() {
		Gson gson = new Gson();
		BufferedReader br;
		try {
			br = new BufferedReader(new FileReader("parameters1.json"));
			systemConfiguration = gson.fromJson(br, SystemConfiguration.class);
			System.out.println(systemConfiguration.toString());
		} catch (FileNotFoundException ex) {
			ex.printStackTrace();
		}
	}

	public void scenario1() throws Exception {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			connect = DriverManager.getConnection(systemConfiguration
					.getConnectionString());
			writeResultSet1(resultSet);
		} catch (Exception e) {
			throw e;
		} finally {
			close();
		}
	}

	public void scenario2() throws Exception {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			connect = DriverManager.getConnection(systemConfiguration
					.getConnectionString());
			writeResultSet2(resultSet);
		} catch (Exception e) {
			throw e;
		} finally {
			close();
		}
	}

	/**
	 * Displays all data of table USERS plus the total number of records
	 * 
	 * @param resultSet
	 * @throws SQLException
	 */
	private void writeResultSet1(ResultSet resultSet) throws SQLException {
		statement = connect.createStatement();
		resultSet = statement
				.executeQuery("select id, user_id, from_unixtime(begin) as begin, from_unixtime(end) as end from users");
		int c = 0;
		while (resultSet.next()) {
			int id = resultSet.getInt("id");
			String user_id = resultSet.getString("user_id");
			Date date1 = resultSet.getDate("begin");
			Date date2 = resultSet.getDate("end");
			System.out
					.printf("User: %d %s %s %s \n", id, user_id, date1, date2);
			c++;
		}
		System.out.printf("Total subscribers =%d\n", c);
	}

	private void writeResultSet2(ResultSet resultSet) throws SQLException {
		statement = connect.createStatement();
		resultSet = statement
				.executeQuery("select src_id, count(*) as callsno "
						+ "from calls " + "group by src_id "
						+ "order by callsno asc");
		int c = 0;
		while (resultSet.next()) {
			String user_id = resultSet.getString("src_id");
			int callsno = resultSet.getInt("callsno");
			System.out.printf("User: %s total calls=%,d \n", user_id, callsno);
			c++;
		}
		System.out.printf("Total records = %d", c);
	}

	private void close() {
		try {
			if (resultSet != null) {
				resultSet.close();
			}

			if (statement != null) {
				statement.close();
			}

			if (connect != null) {
				connect.close();
			}
		} catch (Exception e) {

		}
	}

	public void scenario3() throws Exception {
		Calendar begin_time = Calendar.getInstance();
		// begin_time is 01/07/2010 00:00:00
		begin_time.clear();
		begin_time.set(Calendar.YEAR, 2010);
		begin_time.set(Calendar.MONTH, 6); // 6 is the 7th month
		begin_time.set(Calendar.DATE, 1);
		begin_time.set(Calendar.HOUR_OF_DAY, 0);
		begin_time.set(Calendar.MINUTE, 0);
		begin_time.set(Calendar.SECOND, 0);
		begin_time.set(Calendar.MILLISECOND, 0);

		Calendar end_time = Calendar.getInstance();
		// end_time is 31/07/2010 00:00:00
		end_time.clear();
		end_time.set(Calendar.YEAR, 2010);
		end_time.set(Calendar.MONTH, 6);
		end_time.set(Calendar.DATE, 3);
		// end_time.set(Calendar.DATE, 31);
		end_time.set(Calendar.HOUR_OF_DAY, 0);
		end_time.set(Calendar.MINUTE, 0);
		end_time.set(Calendar.SECOND, 0);
		end_time.set(Calendar.MILLISECOND, 0);

		ClustersDataGenerator cdg = new ClustersDataGenerator(begin_time,
				end_time);
		// List<SubscriberCompactRecord> aList = cdg.generateRecords();
		cdg.exportData();
		Clusterer clusterer = new Clusterer();
		clusterer.cluster();
	}

	public void scenario4() {
		SystemConfiguration sc = new SystemConfiguration();
		sc.setSomLearningRate(0.7);
		sc.setDbName("cdr_db");
		RelationalDatabaseManagementSystem rdbms = new RelationalDatabaseManagementSystem();
		rdbms.setIp("127.0.0.1");
		rdbms.setPort(3306);
		rdbms.setUser("root");
		rdbms.setPassword("");
		sc.setRdbms(rdbms);
		TrainingParameters trainingParameters = new TrainingParameters();
		trainingParameters.setMinimumNumberOfCalls(50);
		trainingParameters.setTrainingPeriodStepInMinutes(60);
		sc.setTrainingParameters(trainingParameters);
		DetectionParameters detectionParameters = new DetectionParameters();
		detectionParameters.setAlertThreshold(0.01);
		sc.setDetectionParameters(detectionParameters);

		// Gson gson = new Gson();
		Gson gson = new GsonBuilder().setPrettyPrinting().create();
		String jSon = gson.toJson(sc);

		try {
			FileWriter file = new FileWriter("parameters.json");
			file.write(gson.toJson(sc));
			file.flush();
			file.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
		System.out.println(jSon);
	}

	public void scenario5() {
		Gson gson = new Gson();
		BufferedReader br;
		try {
			br = new BufferedReader(new FileReader("parameters.json"));
			SystemConfiguration sc = gson.fromJson(br,
					SystemConfiguration.class);
			System.out.println(sc.toString());
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		}
	}

	public void scenario6() throws Exception {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			connect = DriverManager.getConnection(systemConfiguration
					.getConnectionString());
			writeResultSet6(resultSet);
		} catch (Exception e) {
			throw e;
		} finally {
			close();
		}
	}

	private void writeResultSet6(ResultSet resultSet) throws SQLException {
		statement = connect.createStatement();
		resultSet = statement.executeQuery("SELECT status, count(*) callsno "
				+ "FROM calls " + "group by status " + "order by callsno desc");
		List<Integer> a = new ArrayList<Integer>();
		List<Integer> b = new ArrayList<Integer>();
		double sum = 0.0;
		while (resultSet.next()) {
			int status = resultSet.getInt("status");
			int callsno = resultSet.getInt("callsno");
			sum += callsno;
			a.add(status);
			b.add(callsno);
		}

		for (int i = 0; i < a.size(); i++) {
			System.out.printf("Status: %d total calls=%,d percentage=%.2f\n",
					a.get(i), b.get(i), b.get(i) / sum * 100);

		}
	}

	private void scenario7() {
		int i = 0;
		for (String fraudster : voz_fraudsters) {
			i++;
			System.out.printf("%d --> %s\n", i, fraudster);
		}
		System.out.printf("Select subscriber ");
		Scanner in = new Scanner(System.in);
		int choice = in.nextInt();
		String selected_subscriber = voz_fraudsters[choice - 1];
		System.out.printf("Selected subscriber %s\n", selected_subscriber);
		try {
			Class.forName("com.mysql.jdbc.Driver");
			connect = DriverManager.getConnection(systemConfiguration
					.getConnectionString());

			PreparedStatement pstmt = connect
					.prepareStatement("SELECT avg(duration), max(duration), count(*) FROM calls where src_id=?");
			pstmt.setString(1, selected_subscriber);
			resultSet = pstmt.executeQuery();
			resultSet.next();
			System.out
					.printf("Average duration=%.2f, Max duration=%.2f, Number of calls=%d\n",
							resultSet.getDouble(1), resultSet.getDouble(2),
							resultSet.getInt(3));
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	private void scenario8() throws SQLException {
		String NL = System.getProperty("line.separator");
		SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		SimpleDateFormat sdf2 = new SimpleDateFormat("yyyy-MM-dd");
		SimpleDateFormat sdf3 = new SimpleDateFormat("HH:mm:ss");
		try {
			Class.forName("com.mysql.jdbc.Driver");
			connect = DriverManager.getConnection(systemConfiguration
					.getConnectionString());
			String fileName = "exported_calls.txt";
			Writer output = new BufferedWriter(new FileWriter(fileName));
			statement = connect.createStatement();
			resultSet = statement
					.executeQuery("select min(id) a, max(id) b from calls");
			resultSet.next();
			int min = resultSet.getInt("a");
			int max = resultSet.getInt("b");
			statement.close();
			System.out.printf("min id=%,d max id=%,d\n", min, max);
			int step = 50000;
			int c = 0;
			for (int i = min; i <= max; i += step) {
				int a = i;
				int b = i + step - 1;
				if (b > max)
					max = b;
				String sql = "select src_id, dst_id, from_unixtime(timestamp) ts, duration, status, src_ip, dst_ip from calls where id >= ? and id <=?";
				PreparedStatement pstmt = connect.prepareStatement(sql);
				pstmt.setInt(1, a);
				pstmt.setInt(2, b);
				resultSet = pstmt.executeQuery();
				while (resultSet.next()) {
					StringBuilder sb = new StringBuilder();
					sb.append(resultSet.getString("src_id"));
					sb.append(" ");
					sb.append(resultSet.getString("dst_id"));
					sb.append(" ");
					java.util.Date ts = sdf1.parse(resultSet.getString("ts"));
					sb.append(sdf2.format(ts));
					sb.append(" ");
					sb.append(sdf3.format(ts));
					sb.append(" ");
					sb.append(resultSet.getInt("duration"));
					sb.append(" ");
					sb.append(resultSet.getInt("status"));
					sb.append(" ");
					sb.append(resultSet.getString("src_ip"));
					sb.append(" ");
					sb.append(resultSet.getString("dst_ip"));
					sb.append(NL);
					output.write(sb.toString());
					c++;
					// System.out.print(sb.toString());
				}
				resultSet.close();
				pstmt.close();
				System.out
						.printf("%,d records exported having id from %,d to %,d (%.2f %% completed)\n",
								c, a, b, (double) b / (double) max * 100);
				output.flush();
			}
			output.close();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (ParseException e) {
			e.printStackTrace();
		} finally {
			close();
		}
	}

	// cannot load all data in memory
	private void scenario8a() throws SQLException {
		String NL = System.getProperty("line.separator");
		SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		SimpleDateFormat sdf2 = new SimpleDateFormat("yyyy-MM-dd");
		SimpleDateFormat sdf3 = new SimpleDateFormat("HH:mm:ss");
		try {
			Class.forName("com.mysql.jdbc.Driver");
			connect = DriverManager.getConnection(systemConfiguration
					.getConnectionString());
			String fileName = "exported_calls.txt";
			Writer output = new BufferedWriter(new FileWriter(fileName));
			String sql = "select src_id, dst_id, from_unixtime(timestamp) ts, duration, status, src_ip, dst_ip from calls limit 1000";
			statement = connect.createStatement();
			resultSet = statement.executeQuery(sql);
			int c = 0;
			while (resultSet.next()) {
				StringBuilder sb = new StringBuilder();
				sb.append(resultSet.getString("src_id"));
				sb.append(" ");
				sb.append(resultSet.getString("dst_id"));
				sb.append(" ");
				java.util.Date ts = sdf1.parse(resultSet.getString("ts"));
				sb.append(sdf2.format(ts));
				sb.append(" ");
				sb.append(sdf3.format(ts));
				sb.append(" ");
				sb.append(resultSet.getInt("duration"));
				sb.append(" ");
				sb.append(resultSet.getInt("status"));
				sb.append(" ");
				sb.append(resultSet.getString("src_ip"));
				sb.append(" ");
				sb.append(resultSet.getString("dst_ip"));
				sb.append(NL);
				output.write(sb.toString());
				c++;
				if (c % 10000 == 0)
					System.out.printf("%d records exported\n", c);
				// System.out.print(sb.toString());
			}
			output.close();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (ParseException e) {
			e.printStackTrace();
		} finally {
			close();
		}
	}
}
